* Table 7, conditional distributions

use "temp/EIA_clean.dta", clear
keep if year==1994
gen sales_norm = salestot / totalsales // normalize by total sales
gen lsales = log(sales_norm)
egen p25 = pctile(lsales), p(25)
egen p50 = pctile(lsales), p(50)
egen p75 = pctile(lsales), p(75)
egen p95 = pctile(lsales), p(95)

* avg import and export shares by quartile of sales, full population
gen quartile = .
replace quartile=1 if lsales<=p25
replace quartile=2 if lsales>p25 & lsales<=p50
replace quartile=3 if lsales>p50 & lsales<=p75
replace quartile=4 if lsales>p75
replace quartile=5 if lsales>p95 // check at top
preserve
	collapse (mean) si xi, by(year quartile)
	reshape wide si xi, i(year) j(quartile)
	order year si1 si2 si3 si4, first	
	foreach x of var si* xi* {
	replace `x' = `x' * 100
	}
	label var si1 "Quartile 1, import share"
	label var si2 "Quartile 2, import share"
	label var si3 "Quartile 3, import share"
	label var si4 "Quartile 4, import share"
	label var xi1 "Quartile 1, export share"
	label var xi2 "Quartile 2, export share"
	label var xi3 "Quartile 3, export share"
	label var xi4 "Quartile 4, export share"	
	export excel si1 si2 si3 si4 xi1 xi2 xi3 xi4 using "../../Output/Table7_Data", first(varl) sh("Top") replace
restore

* avg export share by quartile of import share, global firms
preserve
	keep if MX==1 
	egen sip25 = pctile(si), p(25)
	egen sip50 = pctile(si), p(50)
	egen sip75 = pctile(si), p(75)
	egen sip95 = pctile(si), p(95)
	gen quartileImp = .
	replace quartileImp=1 if si<=sip25
	replace quartileImp=2 if si>sip25 & si<=sip50
	replace quartileImp=3 if si>sip50 & si<=sip75
	replace quartileImp=4 if si>sip75
	replace quartileImp=5 if si>sip95
	collapse (mean) xi, by(year quartileImp)
	reshape wide xi, i(year) j(quartileImp)
	foreach x of var xi* {
	replace `x' = `x' * 100
	}
	label var xi1 "Quartile 1, export share"
	label var xi2 "Quartile 2, export share"
	label var xi3 "Quartile 3, export share"
	label var xi4 "Quartile 4, export share"
	export excel xi1 xi2 xi3 xi4 using "../../Output/Table7_Data", first(varl) sh("Bottom")
restore
